package com.eryos.android.cigarettecounter.database;

import java.util.List;
import java.util.Map;

import android.database.Cursor;
import android.util.Log;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;

/**
 * Project : CigaretteCounter Author : Olivier Combe URL : http://eryos.fr/CigaretteCounter/
 * 
 * Copyright(C)2011 - Olivier Combe
 * 
 * Licensed under the Apache License, Version 2.0(the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 * 
 * @version \$Revision.*\$
 * @author Olivier Combe
 */
public class WeeklyAdapterImpl implements ExtendedPeriodicAdapterInterface {

    private final CigaretteDatabase currentDatabase;

    /**
     * Create a new WeeklyAdapter for the database in parameters.
     * 
     * @param db
     *            for which an adapter is needed
     */
    public WeeklyAdapterImpl(final CigaretteDatabase db) {
        currentDatabase = db;
    }

    /**
     * Return the number of cigarettes smoked this week.
     * 
     * @return the number of cigarettes smoked this week
     */
    @Override
    public int getCount() {
        Log.i(this.getClass().getSimpleName(), "get Weekly Count");
        String sqlQuery = "SELECT   count(1)Nb " + "FROM     " + DBConnector.DATABASE_TABLE + " " + "WHERE    "
                + DBFields.DB_WEEK + " = " + DBFields.THIS_WEEK + "; ";

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        return DBTools.getFirstIntResult(mCursor);
    }

    /**
     * Return the number of cigarettes smoked this week.
     * 
     * @return the number of cigarettes smoked this week.
     */
    @Override
    public double getCost() {
        Log.i(this.getClass().getSimpleName(), "get Weekly Cost");
        String sqlQuery = "SELECT   round(sum(ifnull(smoke_valid,0)), " + DBTools.ROUNDING + ")Nb " + "FROM     "
                + DBConnector.DATABASE_TABLE + " " + "WHERE    strftime('%W', smoke_time) = " + DBFields.THIS_WEEK
                + "; ";

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        return DBTools.getFirstDoubleResult(mCursor);
    }

    /**
     * Return the average number of cigarettes smoked by day this week.
     * 
     * @return the average number of cigarettes smoked by day this week.
     */
    @Override
    public double getAverage() {
        Log.i(this.getClass().getSimpleName(), "get Weekly Average");
        try {
            String sqlQuery = "SELECT sum(nb), count(nb), round(avg(nb), " + DBTools.ROUNDING + ")" + "FROM  " + "("
                    + "    SELECT   " + DBFields.DATE + " Day, " + DBFields.COUNT + "    FROM     "
                    + DBConnector.DATABASE_TABLE + " " + "    WHERE    strftime('%W', smoke_time) = "
                    + DBFields.THIS_WEEK + " " + "    GROUP BY " + DBFields.DATE + "    ORDER BY " + DBFields.DATE
                    + ");";

            double res = -1;

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            if (mCursor != null) {
                mCursor.moveToFirst();
                res = mCursor.getDouble(2);
            }

            return res;
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getAverage", e);
            return -1;
        }
    }

    /**
     * Return the number of cigarettes smoked each day of the week.
     * 
     * @return the number of cigarettes smoked each day of the week.
     */
    @Override
    public Map<Integer, SmokeCounter> getDetails() {
        Log.i(this.getClass().getSimpleName(), "get Weekly Details");
        String sqlQuery =
        // "SELECT   strftime('%w', smoke_time)Day, count(1)Nb  " +
        "SELECT   strftime('%w', smoke_time)Day, count(1)Nb, strftime('%d', smoke_time)Day, strftime('%m', smoke_time)Month, strftime('%Y', smoke_time)Year  "
                + "FROM     "
                + DBConnector.DATABASE_TABLE
                + " "
                + "WHERE    strftime('%W', smoke_time) = "
                + DBFields.THIS_WEEK
                + " "
                + "GROUP BY strftime('%w', smoke_time)"
                + "ORDER BY strftime('%Y-%m-%d', smoke_time); ";

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        // return DBTools.parseCursorAsSmokeCounter(mCursor);
        return DBTools.parseCursorAsSmokeCounterSet(mCursor);
    }

    /**
     * Return the number of cigarettes smoked each day of the week.
     * 
     * @return the number of cigarettes smoked each day of the week.
     */
    @Override
    public Map<Integer, SmokeCounter> getPreviousDetails() {
        Log.i(this.getClass().getSimpleName(), "get Weekly Details");
        String sqlQuery =
        // "SELECT   strftime('%w', smoke_time)Day, count(1)Nb  " +
        "SELECT   strftime('%w', smoke_time)Day, count(1)Nb, strftime('%d', smoke_time)Day, strftime('%m', smoke_time)Month, strftime('%Y', smoke_time)Year  "
                + "FROM     "
                + DBConnector.DATABASE_TABLE
                + " "
                + "WHERE    strftime('%W', smoke_time) = "
                + DBFields.PREVIOUS_WEEK
                + " "
                + "GROUP BY strftime('%w', smoke_time)"
                + "ORDER BY strftime('%Y-%m-%d', smoke_time); ";

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        // return DBTools.parseCursorAsSmokeCounter(mCursor);
        return DBTools.parseCursorAsSmokeCounterSet(mCursor);
    }

    /**
     * Return the average number of cigarettes smoked by day for the last 6 weeks.
     * 
     * @return the average number of cigarettes smoked by day for the last 6 weeks.
     */
    @Override
    public List<SmokeAverage> getHistoryAverage() {
        Log.i(this.getClass().getSimpleName(), "get Last 6 Weekly History");
        String sqlQuery = "SELECT Week, sum(nb), count(nb), round(avg(nb), " + DBTools.ROUNDING + ")" + "FROM  " + "("
                + "    SELECT   strftime('%W', smoke_time)Week, strftime('%Y-%m-%d', smoke_time)Day, count(1)Nb  "
                + "    FROM     " + DBConnector.DATABASE_TABLE + " " + "    WHERE    strftime('%Y-%W', smoke_time) > "
                + DBFields.SIX_WEEKS_AGO + " "
                + "    GROUP BY strftime('%W', smoke_time), strftime('%d/%m/%Y', smoke_time)" + ")" + "GROUP BY Week "
                + "ORDER BY Day; ";

        Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

        return DBTools.parseCursorAsSmokeAverage(mCursor);
    }

    /**
     * Return the average number of cigarettes smoked by day for the last 6 month.
     * 
     * @return the average number of cigarettes smoked by day for the last 6 month.
     */
    @Override
    public List<SmokeAverage> getHistoryCost() {
        Log.i(this.getClass().getSimpleName(), "get Last 6 Months Costs");
        try {
            String sqlQuery = "SELECT Month, sum(cost), count(cost), round(sum(ifnull(cost,0)), "
                    + DBTools.ROUNDING
                    + ")"
                    + "FROM "
                    + "("
                    + "    SELECT   strftime('%m', smoke_time)Month, strftime('%Y-%m-%d', smoke_time)Day, sum(smoke_valid)cost  "
                    + "    FROM     " + DBConnector.DATABASE_TABLE + " "
                    + "    WHERE    strftime('%Y-%W', smoke_time) > " + DBFields.SIX_WEEKS_AGO + " "
                    + "    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time)ORDER BY smoke_time"
                    + ")" + "GROUP BY Month " + "ORDER BY Day; ";

            Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

            return DBTools.parseCursorAsSmokeAverage(mCursor);
        } catch (Exception e) {
            Log.e(this.getClass().getSimpleName(), "getHistoryCost", e);
            return null;
        }
    }

}
